<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*" %>
				<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");

						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);

							// Create the prepared statement and use it to
							// INSERT the student username INTO the account table.
							PreparedStatement pstmt = conn
									.prepareStatement("INSERT INTO account VALUES (?, ?, ?)");
							pstmt.setString(1, request.getParameter("username"));
							pstmt.setString(2, "ChangeMe");
							pstmt.setString(3, request.getParameter("role"));
							pstmt.executeUpdate();
							
							// INSERT the faculty attributes INTO the faculty table.
							pstmt = conn.prepareStatement(
	                		"INSERT INTO faculty VALUES (?, ?, ?, ?)");
			
							pstmt.setString(1, request.getParameter("name"));
							pstmt.setString(2, request.getParameter("title"));
							pstmt.setString(3, request.getParameter("department"));
							pstmt.setString(4, request.getParameter("username"));
							pstmt.executeUpdate();
							
							String[] students = request.getParameterValues("students");
							if (students != null) {
								for (String student : students) {
									pstmt = conn.prepareStatement("INSERT INTO advice VALUES (?, ?)");
								
									pstmt.setString(1, student);
									pstmt.setString(2, request.getParameter("name"));
									pstmt.executeUpdate();
								}
							}
							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // UPDATE the student attributes in the Student table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "UPDATE faculty SET department = ?, title = ?  WHERE name = ?");
	                        
	                        pstmt.setString(1, request.getParameter("department"));
	                        pstmt.setString(2, request.getParameter("title"));
							pstmt.setString(3, request.getParameter("name"));
	                        pstmt.executeUpdate();

	                     	// Insert the advice relationships in the advice table.
	                     	// Avoid insert duplicates entries by deleting all exising entries first.
	                     	pstmt = conn.prepareStatement("DELETE FROM advice WHERE faculty_name = ?");
	                     	pstmt.setString(1, request.getParameter("name"));
	                     	pstmt.executeUpdate();

	                     	String[] students = request.getParameterValues("students");
	                     	if (students != null) {
								for (String student : students) {
	                     			pstmt = conn.prepareStatement("INSERT INTO advice VALUES (?, ?)");
									pstmt.setString(1, student);
									pstmt.setString(2, request.getParameter("name"));
									pstmt.executeUpdate();
								}
	                     	}
	                    
	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						if (action != null && action.equals("delete")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        String faculty_name = request.getParameter("name");
	                        
	                        //delete advive
	                        PreparedStatement pstmt = conn.prepareStatement("DELETE FROM advice WHERE faculty_name=?");
	                        pstmt.setString(1, faculty_name);
	                        pstmt.executeUpdate();
	                        
	                        //delete committee
	                        pstmt = conn.prepareStatement("DELETE FROM committee WHERE faculty_name=?");
	                        pstmt.setString(1, faculty_name);
	                        pstmt.executeUpdate();
	                        
	                        //get section_id that the faculty is teaching
	                        pstmt = conn.prepareStatement("SELECT section_id FROM section WHERE faculty_name=?");
	                        pstmt.setString(1, faculty_name);
	                        ResultSet rs = pstmt.executeQuery();
	                        while(rs.next()){		
	                        	//delete meeting
	                        	pstmt = conn.prepareStatement("DELETE FROM meeting WHERE section_id=?");
	                        	pstmt.setInt(1, rs.getInt("section_id"));
	                        	pstmt.executeUpdate();
	                        	//delete review_session
	                        	pstmt = conn.prepareStatement("DELETE FROM review_session WHERE section_id=?");
	                        	pstmt.setInt(1, rs.getInt("section_id"));
	                        	pstmt.executeUpdate();
	                        	//delete enroll
								pstmt = conn.prepareStatement("DELETE FROM enroll WHERE section_id=?");
	                        	pstmt.setInt(1, rs.getInt("section_id"));
	                        	pstmt.executeUpdate();
								//delete section
	                        	pstmt = conn.prepareStatement("DELETE FROM section WHERE section_id=?");
	                        	pstmt.setInt(1, rs.getInt("section_id"));
	                        	pstmt.executeUpdate();
	                        }
							
							//delete faculty and username
	                        pstmt = conn.prepareStatement("DELETE faculty WHERE name = ?");
	                        pstmt.setString(1, request.getParameter("name"));
	                        pstmt.executeUpdate();                             
	                        pstmt = conn.prepareStatement("DELETE account WHERE username = ?");
		                    pstmt.setString(1, request.getParameter("username"));
		                    pstmt.executeUpdate();
                  
	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
					
						// Create the statement
						Statement statement = conn.createStatement();
						
						// Use the created statement to SELECT
						// the student attributes FROM the student table.
						SortedMap<String, String> students = new TreeMap<String, String>();
						ResultSet rsMS = statement.executeQuery("SELECT s.student_id, first_name, last_name " +
								"FROM student s, ms_student m WHERE s.student_id = m.student_id");
						while (rsMS.next()) {
							String id = rsMS.getString("student_id");
							String name = rsMS.getString("first_name") + " " + rsMS.getString("last_name");
							students.put(id, name);	
						}
						ResultSet rsPhD = statement.executeQuery("SELECT s.student_id, first_name, last_name " +
								"FROM student s, phd_student p WHERE s.student_id = p.student_id");
						while (rsPhD.next()) {
							String id = rsPhD.getString("student_id");
							String name = rsPhD.getString("first_name") + " " + rsPhD.getString("last_name");
							students.put(id, name);	
						}
						
						ResultSet rsDept = statement.executeQuery("SELECT * FROM department");
						// Save departments for later use.
						List<String> departments = new ArrayList<String>();
						while (rsDept.next()) {
							departments.add(rsDept.getString("department"));
						}

						// Use the created statement to SELECT
						// the faculty attributes FROM the faculty table.
						ResultSet rsFaculty = statement.executeQuery("SELECT * FROM faculty");
						CachedRowSet rs = new CachedRowSetImpl();
				        rs.populate(rsFaculty);
				%>
				<table border="1">
					<tr>
						<th>Name</th>
						<th>Username</th>
						<th>Department</th>
						<th>Title</th>
						<th>Students</th>
					</tr>
					<tr>
						<form action="faculty.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							<input type="hidden" value="faculty" name="role">
							<th><input value="" name="name" size="20"></th>
							<th><input value="" name="username" size="10"></th>
							<th>
								<select name="department" style="width:100px">
								<%
									for (String department : departments) {
								%>
									<option value="<%= department %>"><%= department %></option>
								<% 
									}
								%>
								</select>
							</th>
							<th><input value="" name="title" size="15"></th>
							<th>
								 <select name="students" size="3" multiple="true">
							 	<%
							 		for (Map.Entry<String, String> student : students.entrySet()) {
							 	%>
						 			<option value="<%= student.getKey() %>">
						 				<%= student.getValue() + " (" + student.getKey() + ")" %>
						 			</option>
								<%
							 		}
							 	%>
								 </select>
							</th>
							<th><input type="submit" value="Insert"></th>
						</form>
					</tr>
					<%
						// Iterate over the ResultSet
						while (rs.next()) {  
					%>
					<tr>
                        <form action="faculty.jsp" method="POST">
                            <input type="hidden" value="update" name="action">
                            <td>
                            	<input type="hidden" value="<%= rs.getString("name") %>" name="name">
                                <%= rs.getString("name") %>
                            </td>
                            <td>
                                <input type="hidden" value="<%= rs.getString("username") %>" name="username">
                                <%= rs.getString("username") %>
                            </td>
                            <td>
                            	<% String dept = rs.getString("department"); %>
                            	<select name="department" style="width:100px">
								<%
									for (String department : departments) {
								%>
									<option value="<%= department %>" 
											<% if (dept.equals(department)) out.print("selected='selected'"); %>>
										<%= department %>
									</option>
								<% 
									}
								%>
                            </td>
                            <td>
                                <input value="<%= rs.getString("title") %>" name="title" size="15">
                            </td>
                            <td>
                            	<%
                            		PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM advice WHERE faculty_name = ?");
                            		pstmt.setString(1, rs.getString("name"));	
                            		ResultSet rsAdvice = pstmt.executeQuery();
                            		Set<String> advicingStudents = new HashSet<String>();
                            		while (rsAdvice.next()) {
                            			advicingStudents.add(rsAdvice.getString("student_id"));
                            		}
                            	%>
                            	<select name="students" size="3" multiple="true">
								 	<%
								 		for (Map.Entry<String, String> student : students.entrySet()) {
								 	%>
								 			<option value="<%= student.getKey() %>" 
								 					<% if (advicingStudents.contains(student.getKey())) out.print("selected='true'"); %>">
								 				<%= student.getValue() + " (" + student.getKey() + ")" %>
								 			</option>
									<%
								 		}
								 	%>
								 </select>
                            </td>
                            <td>
                                <input type="submit" value="Update">
                            </td>
                        </form>
                        <form action="faculty.jsp" method="POST">
                            <input type="hidden" value="delete" name="action">
                            <input type="hidden" 
                                value="<%= rs.getString("name") %>" name="name">
                            <input type="hidden" 
                                value="<%= rs.getString("username") %>" name="username">
                            <td>
                                <input type="submit" value="Delete">
                            </td>
                        </form>
                    </tr>
					<%
							}
					%>
					<%
						} catch (SQLException e) {
							System.out.println(e.getMessage());
							conn.rollback();
						} catch (Exception e) {
							System.out.println(e.getMessage());
							conn.rollback();
						}
					%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>
